namespace CISLib
{
    using System;
    using System.Collections;
    using System.Data;
    using System.Data.SqlClient;
    using System.Text;

    public class RemindCustomerInfo
    {
        public static ArrayList CustomerBirthDate(Guid customerId, int day)
        {
            StringBuilder sb = new StringBuilder();
            SqlDataReader MyReader = null;
            ArrayList List = new ArrayList();
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            sb.Append("select TB_CustomerBasicInfo.CompanyId,                                                            ");
            sb.Append("      TB_CustomerBasicInfo. CustomerTitle,                                                            ");
            sb.Append("      TB_CustomerBasicInfo. CustomerName,                                                                ");
            sb.Append("\t   TB_CustomerPersonalInfo.CustomerBirthDate,                                                       ");
            sb.Append("\t   datepart(dy,TB_CustomerPersonalInfo.CustomerBirthDate) - datename(dy,getdate()) as ResidualDay,    ");
            sb.Append("\t   datepart(yyyy,getdate())-datepart(yyyy,TB_CustomerPersonalInfo.CustomerBirthDate) as Interval    ");
            sb.Append("from dbo.TB_CustomerPersonalInfo left outer join dbo.TB_CustomerBasicInfo  on   dbo.TB_CustomerPersonalInfo.customerid = dbo.TB_CustomerBasicInfo.customerid                      ");
            sb.Append("where                                                                                                   ");
            sb.Append("datepart(dy,TB_CustomerPersonalInfo.CustomerBirthDate) - datename(dy,getdate())  <=@Day                    ");
            sb.Append("and                                                                                                     ");
            sb.Append("datepart(dy,TB_CustomerPersonalInfo.CustomerBirthDate) - datename(dy,getdate())  >=0\t\t               ");
            sb.Append("and                                                                            ");
            sb.Append("TB_CustomerPersonalInfo.CustomerBirthDate <> '1800-1-1' ");
            sb.Append("and  TB_CustomerPersonalInfo.customerId in                                         ");
            sb.Append("(select TB_CustomerBasicInfo.customerid from TB_CustomerBasicInfo              ");
            sb.Append("where                                                                           ");
            sb.Append("TB_CustomerBasicInfo.customerid = @CustomerId   )                                  ");
            string strSQL = sb.ToString();
            MyDB.AddParameters(day, "Day", SqlDbType.Int);
            MyDB.AddParameters(customerId, "CustomerId", SqlDbType.UniqueIdentifier);
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteReader();
            MyReader = MyDB.DBReader;
            while (MyReader.Read())
            {
                RemindCustomerDate remindCustomerDate = new RemindCustomerDate();
                remindCustomerDate.CompanyId = MyReader.GetGuid(0);
                remindCustomerDate.CustomerTitle = MyReader.GetString(1);
                remindCustomerDate.CustomerName = MyReader.GetString(2);
                remindCustomerDate.CustomerDate = MyReader.GetDateTime(3);
                remindCustomerDate.ResidualDay = MyReader.GetInt32(4);
                remindCustomerDate.Interval = MyReader.GetInt32(5);
                List.Add(remindCustomerDate);
            }
            MyDB.DBClose();
            return List;
        }

        public static DataSet CustomerBirthDate1(Guid catId, Guid regionId, int day)
        {
            StringBuilder sb = new StringBuilder();
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            sb.Append("select TB_CustomerBasicInfo.CustomerName,                                                            ");
            sb.Append("      TB_CustomerBasicInfo. CustomerTitle,                                                            ");
            sb.Append("      dbo.TB_CompanyInfo.CompanyName,                                                                 ");
            sb.Append("\t   TB_CustomerPersonalInfo.CustomerBirthDate,                                                       ");
            sb.Append("\t   datepart(dy,TB_CustomerPersonalInfo.CustomerBirthDate) - datename(dy,getdate()) as ResidualDay    ");
            sb.Append("from dbo.TB_CustomerPersonalInfo, dbo.TB_CustomerBasicInfo,dbo.TB_CompanyInfo                            ");
            sb.Append("where                                                                                                   ");
            sb.Append("datepart(dy,TB_CustomerPersonalInfo.CustomerBirthDate) - datename(dy,getdate()) <=@Day                    ");
            sb.Append("and                                                                                                     ");
            sb.Append("datepart(dy,TB_CustomerPersonalInfo.CustomerBirthDate) - datename(dy,getdate()) >=0\t\t               ");
            sb.Append("and \t\t                                                                                               ");
            sb.Append("dbo.TB_CustomerBasicInfo.CustomerID = TB_CustomerPersonalInfo.CustomerID                                  ");
            sb.Append("and                                                                                                     ");
            sb.Append(" dbo.TB_CustomerBasicInfo.CompanyId = dbo.TB_CompanyInfo.CompanyId            ");
            sb.Append("and                                                                            ");
            sb.Append("TB_CustomerPersonalInfo.CustomerBirthDate <> '1800-1-1' ");
            if ((catId.ToString() != "00000000-0000-0000-0000-000000000000") || (regionId.ToString() != "00000000-0000-0000-0000-000000000000"))
            {
                if (catId.ToString() == "00000000-0000-0000-0000-000000000000")
                {
                    MyDB.AddParameters(regionId, "RegionId", SqlDbType.UniqueIdentifier);
                    sb.Append("and   TB_CustomerBasicInfo.RegionId = @RegionId     ");
                }
                else if (regionId.ToString() == "00000000-0000-0000-0000-000000000000")
                {
                    MyDB.AddParameters(catId, "CATId", SqlDbType.UniqueIdentifier);
                    sb.Append("and   TB_CustomerBasicInfo.CATId = @CATId     ");
                }
                else
                {
                    MyDB.AddParameters(catId, "CATId", SqlDbType.UniqueIdentifier);
                    MyDB.AddParameters(regionId, "RegionId", SqlDbType.UniqueIdentifier);
                    sb.Append("and  TB_CustomerBasicInfo.CATId = @CATId and TB_CustomerBasicInfo.RegionId = @RegionId ");
                }
            }
            MyDB.AddParameters(day, "Day", SqlDbType.Int);
            string strSQL = sb.ToString();
            MyDB.DBSetSelect(strSQL);
            return MyDB.DBReturnDataSet();
        }

        public static ArrayList CustomerBirthDateInNextYear(Guid customerId, int day)
        {
            StringBuilder sb = new StringBuilder();
            SqlDataReader MyReader = null;
            ArrayList List = new ArrayList();
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            sb.Append("\tselect TB_CustomerBasicInfo.CompanyId,                                ");
            sb.Append("\t\tTB_CustomerBasicInfo. CustomerTitle,                                ");
            sb.Append("\t\tTB_CustomerBasicInfo. CustomerName,                                    ");
            sb.Append("\t\tTB_CustomerPersonalInfo.CustomerBirthDate,                                ");
            sb.Append("\t\tdatepart(dy,TB_CustomerPersonalInfo.CustomerBirthDate)+ datepart(dy,dateadd(ms,-3,DATEADD(yy,  DATEDIFF(yy,0,getdate())+1,  0)))- datepart(dy,getdate()) as ResidualDay,datepart(yyyy,getdate())-datepart(yyyy,TB_CustomerPersonalInfo.CustomerBirthDate) as Interval   ");
            sb.Append("\tfrom dbo.TB_CustomerPersonalInfo left outer join dbo.TB_CustomerBasicInfo  on   dbo.TB_CustomerPersonalInfo.customerid = dbo.TB_CustomerBasicInfo.customerid                     ");
            sb.Append("\tWHERE CustomerBirthDate in                                                   ");
            sb.Append("\t\t(SELECT CustomerBirthDate                                                ");
            sb.Append("\t\tFROM TB_CustomerPersonalInfo                                                 ");
            sb.Append("\t\tWHERE                                                                            ");
            sb.Append("\t\tdatepart(dy,CustomerBirthDate) < datepart(dy,getdate())                               ");
            sb.Append("\t\t)                                                                                           ");
            sb.Append("\t\tand                                                                                                           ");
            sb.Append("\t\tdatepart(dy,dateadd(day,@Day,getdate())) - (datepart(dy,TB_CustomerPersonalInfo.CustomerBirthDate))<=@Day      ");
            sb.Append("\t\tand                                                                                                               ");
            sb.Append("\t\tdatepart(dy,dateadd(day,@Day,getdate())) - (datepart(dy,TB_CustomerPersonalInfo.CustomerBirthDate)) >=0\t          ");
            sb.Append("\t\tand                                                                                                              ");
            sb.Append("\t\tTB_CustomerPersonalInfo.CustomerBirthDate <> '1800-1-1'                                                          ");
            sb.Append("\t\tand  TB_CustomerPersonalInfo.customerId in                                                                         ");
            sb.Append("\t\t\t(select TB_CustomerBasicInfo.customerid from TB_CustomerBasicInfo                                             ");
            sb.Append("\t\t\twhere                                                                                                        ");
            sb.Append("\t\t\tTB_CustomerBasicInfo.customerid = @CustomerId )                                                               ");
            string strSQL = sb.ToString();
            MyDB.AddParameters(day, "Day", SqlDbType.Int);
            MyDB.AddParameters(customerId, "CustomerId", SqlDbType.UniqueIdentifier);
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteReader();
            MyReader = MyDB.DBReader;
            while (MyReader.Read())
            {
                RemindCustomerDate remindCustomerDate = new RemindCustomerDate();
                remindCustomerDate.CompanyId = MyReader.GetGuid(0);
                remindCustomerDate.CustomerTitle = MyReader.GetString(1);
                remindCustomerDate.CustomerName = MyReader.GetString(2);
                remindCustomerDate.CustomerDate = MyReader.GetDateTime(3);
                remindCustomerDate.ResidualDay = MyReader.GetInt32(4);
                remindCustomerDate.Interval = MyReader.GetInt32(5);
                List.Add(remindCustomerDate);
            }
            MyDB.DBClose();
            return List;
        }

        public static ArrayList CustomerMarriageDate(Guid customerId, int day)
        {
            StringBuilder sb = new StringBuilder();
            SqlDataReader MyReader = null;
            ArrayList List = new ArrayList();
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            sb.Append("select TB_CustomerBasicInfo.CompanyId,                                                            ");
            sb.Append("      TB_CustomerBasicInfo. CustomerTitle,                                                            ");
            sb.Append("      TB_CustomerBasicInfo. CustomerName,                                                                ");
            sb.Append("\t   TB_CustomerPersonalInfo.CustomerMarriageDate,                                                       ");
            sb.Append("\t   datepart(dy,TB_CustomerPersonalInfo.CustomerMarriageDate) - datename(dy,getdate()) as ResidualDay,    ");
            sb.Append("\t   datepart(yyyy,getdate())-datepart(yyyy,TB_CustomerPersonalInfo.CustomerMarriageDate) as Interval    ");
            sb.Append("from dbo.TB_CustomerPersonalInfo left outer join dbo.TB_CustomerBasicInfo  on   dbo.TB_CustomerPersonalInfo.customerid = dbo.TB_CustomerBasicInfo.customerid                      ");
            sb.Append("where                                                                                                   ");
            sb.Append("datepart(dy,TB_CustomerPersonalInfo.CustomerMarriageDate) - datename(dy,getdate()) <=@Day          ");
            sb.Append("and                                                                                                     ");
            sb.Append("datepart(dy,TB_CustomerPersonalInfo.CustomerMarriageDate) - datename(dy,getdate()) >=0\t\t               ");
            sb.Append("and                                                                            ");
            sb.Append("TB_CustomerPersonalInfo.CustomerMarriageDate <> '1800-1-1' ");
            sb.Append("and  TB_CustomerPersonalInfo.customerId in                                         ");
            sb.Append("(select TB_CustomerBasicInfo.customerid from TB_CustomerBasicInfo              ");
            sb.Append("where                                                                           ");
            sb.Append("TB_CustomerBasicInfo.customerid = @CustomerId   )                                  ");
            string strSQL = sb.ToString();
            MyDB.AddParameters(day, "Day", SqlDbType.Int);
            MyDB.AddParameters(customerId, "CustomerId", SqlDbType.UniqueIdentifier);
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteReader();
            MyReader = MyDB.DBReader;
            while (MyReader.Read())
            {
                RemindCustomerDate remindCustomerDate = new RemindCustomerDate();
                remindCustomerDate.CompanyId = MyReader.GetGuid(0);
                remindCustomerDate.CustomerTitle = MyReader.GetString(1);
                remindCustomerDate.CustomerName = MyReader.GetString(2);
                remindCustomerDate.CustomerDate = MyReader.GetDateTime(3);
                remindCustomerDate.ResidualDay = MyReader.GetInt32(4);
                remindCustomerDate.Interval = MyReader.GetInt32(5);
                List.Add(remindCustomerDate);
            }
            MyDB.DBClose();
            return List;
        }

        public static DataSet CustomerMarriageDate1(Guid catId, Guid regionId, int day)
        {
            StringBuilder sb = new StringBuilder();
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            sb.Append("select TB_CustomerBasicInfo.CustomerName,                                                            ");
            sb.Append("      TB_CustomerBasicInfo. CustomerTitle,                                                            ");
            sb.Append("      dbo.TB_CompanyInfo.CompanyName,                                                                 ");
            sb.Append("\t   TB_CustomerPersonalInfo.CustomerMarriageDate,                                                       ");
            sb.Append("\t   datepart(dy,TB_CustomerPersonalInfo.CustomerMarriageDate) - datename(dy,getdate()) as ResidualDay    ");
            sb.Append("from dbo.TB_CustomerPersonalInfo, dbo.TB_CustomerBasicInfo,dbo.TB_CompanyInfo                            ");
            sb.Append("where                                                                                                   ");
            sb.Append("datepart(dy,TB_CustomerPersonalInfo.CustomerMarriageDate) - datename(dy,getdate()) <=@Day                    ");
            sb.Append("and                                                                                                     ");
            sb.Append("datepart(dy,TB_CustomerPersonalInfo.CustomerMarriageDate) - datename(dy,getdate()) >=0\t\t               ");
            sb.Append("and \t\t                                                                                               ");
            sb.Append("dbo.TB_CustomerBasicInfo.CustomerID = TB_CustomerPersonalInfo.CustomerID                                  ");
            sb.Append("and                                                                                                     ");
            sb.Append(" dbo.TB_CustomerBasicInfo.CompanyId = dbo.TB_CompanyInfo.CompanyId                         ");
            sb.Append("and                                                                                                 ");
            sb.Append("TB_CustomerPersonalInfo.CustomerMarriageDate <> '1800-1-1'                        ");
            if ((catId.ToString() != "00000000-0000-0000-0000-000000000000") || (regionId.ToString() != "00000000-0000-0000-0000-000000000000"))
            {
                if (catId.ToString() == "00000000-0000-0000-0000-000000000000")
                {
                    MyDB.AddParameters(regionId, "RegionId", SqlDbType.UniqueIdentifier);
                    sb.Append("and   TB_CustomerBasicInfo.RegionId = @RegionId     ");
                }
                else if (regionId.ToString() == "00000000-0000-0000-0000-000000000000")
                {
                    MyDB.AddParameters(catId, "CATId", SqlDbType.UniqueIdentifier);
                    sb.Append("and   TB_CustomerBasicInfo.CATId = @CATId     ");
                }
                else
                {
                    MyDB.AddParameters(catId, "CATId", SqlDbType.UniqueIdentifier);
                    MyDB.AddParameters(regionId, "RegionId", SqlDbType.UniqueIdentifier);
                    sb.Append("and  TB_CustomerBasicInfo.CATId = @CATId and TB_CustomerBasicInfo.RegionId = @RegionId ");
                }
            }
            MyDB.AddParameters(day, "Day", SqlDbType.Int);
            string strSQL = sb.ToString();
            MyDB.DBSetSelect(strSQL);
            return MyDB.DBReturnDataSet();
        }

        public static ArrayList CustomerMarriageDateInNextYear(Guid customerId, int day)
        {
            StringBuilder sb = new StringBuilder();
            SqlDataReader MyReader = null;
            ArrayList List = new ArrayList();
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            sb.Append("\tselect TB_CustomerBasicInfo.CompanyId,                                ");
            sb.Append("\t\tTB_CustomerBasicInfo. CustomerTitle,                                ");
            sb.Append("\t\tTB_CustomerBasicInfo. CustomerName,                                    ");
            sb.Append("\t\tTB_CustomerPersonalInfo.CustomerMarriageDate,                                ");
            sb.Append("\t\tdatepart(dy,TB_CustomerPersonalInfo.CustomerMarriageDate)+ datepart(dy,dateadd(ms,-3,DATEADD(yy,  DATEDIFF(yy,0,getdate())+1,  0)))- datepart(dy,getdate()) as ResidualDay,datepart(yyyy,getdate())-datepart(yyyy,TB_CustomerPersonalInfo.CustomerMarriageDate) as Interval   ");
            sb.Append("\tfrom dbo.TB_CustomerPersonalInfo left outer join dbo.TB_CustomerBasicInfo  on   dbo.TB_CustomerPersonalInfo.customerid = dbo.TB_CustomerBasicInfo.customerid                     ");
            sb.Append("\tWHERE CustomerMarriageDate in                                                   ");
            sb.Append("\t\t(SELECT CustomerMarriageDate                                                ");
            sb.Append("\t\tFROM TB_CustomerPersonalInfo                                                 ");
            sb.Append("\t\tWHERE                                                                            ");
            sb.Append("\t\tdatepart(dy,CustomerMarriageDate) < datepart(dy,getdate())                               ");
            sb.Append("\t\t)                                                                                           ");
            sb.Append("\t\tand                                                                                                           ");
            sb.Append("\t\tdatepart(dy,dateadd(day,@Day,getdate())) - (datepart(dy,TB_CustomerPersonalInfo.CustomerMarriageDate))<=@Day      ");
            sb.Append("\t\tand                                                                                                               ");
            sb.Append("\t\tdatepart(dy,dateadd(day,@Day,getdate())) - (datepart(dy,TB_CustomerPersonalInfo.CustomerMarriageDate)) >=0\t          ");
            sb.Append("\t\tand                                                                                                              ");
            sb.Append("\t\tTB_CustomerPersonalInfo.CustomerMarriageDate <> '1800-1-1'                                                          ");
            sb.Append("\t\tand  TB_CustomerPersonalInfo.customerId in                                                                         ");
            sb.Append("\t\t\t(select TB_CustomerBasicInfo.customerid from TB_CustomerBasicInfo                                             ");
            sb.Append("\t\t\twhere                                                                                                        ");
            sb.Append("\t\t\tTB_CustomerBasicInfo.customerid = @CustomerId )                                                                     ");
            string strSQL = sb.ToString();
            MyDB.AddParameters(day, "Day", SqlDbType.Int);
            MyDB.AddParameters(customerId, "CustomerId", SqlDbType.UniqueIdentifier);
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteReader();
            MyReader = MyDB.DBReader;
            while (MyReader.Read())
            {
                RemindCustomerDate remindCustomerDate = new RemindCustomerDate();
                remindCustomerDate.CompanyId = MyReader.GetGuid(0);
                remindCustomerDate.CustomerTitle = MyReader.GetString(1);
                remindCustomerDate.CustomerName = MyReader.GetString(2);
                remindCustomerDate.CustomerDate = MyReader.GetDateTime(3);
                remindCustomerDate.ResidualDay = MyReader.GetInt32(4);
                remindCustomerDate.Interval = MyReader.GetInt32(5);
                List.Add(remindCustomerDate);
            }
            MyDB.DBClose();
            return List;
        }

        public static ArrayList FamilyMemberBirthDate(Guid customerId, int day)
        {
            StringBuilder sb = new StringBuilder();
            SqlDataReader MyReader = null;
            ArrayList List = new ArrayList();
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            sb.Append("select TB_CustomerBasicInfo.CompanyId,TB_CustomerBasicInfo.CustomerTitle,TB_CustomerBasicInfo.CustomerName,TB_CustomerFamilyInfo.MemberName,TB_CustomerFamilyInfo.Relationship,TB_CustomerFamilyInfo.MBirthDate,datepart(dy,TB_CustomerFamilyInfo.MBirthDate) - datename(dy,getdate()) as ResidualDay,datepart(yyyy,getdate())-datepart(yyyy,TB_CustomerFamilyInfo.MBirthDate) as Interval");
            sb.Append("  from  dbo.TB_CustomerFamilyInfo left outer join dbo.TB_CustomerBasicInfo  on  dbo.TB_CustomerFamilyInfo.customerId =  dbo.TB_CustomerBasicInfo.customerid                   ");
            sb.Append("where                                                                                     ");
            sb.Append("datepart(dy,TB_CustomerFamilyInfo.MBirthDate) - datename(dy,getdate()) <=@Day          ");
            sb.Append("and                                                                                    ");
            sb.Append("datepart(dy,TB_CustomerFamilyInfo.MBirthDate) - datename(dy,getdate()) >=0        ");
            sb.Append("and                                                                                ");
            sb.Append("TB_CustomerFamilyInfo.MBirthDate <> '1800-1-1'                                       ");
            sb.Append("and  TB_CustomerFamilyInfo.customerId in                                         ");
            sb.Append("(select TB_CustomerBasicInfo.customerid from TB_CustomerBasicInfo              ");
            sb.Append("where                                                                           ");
            sb.Append("TB_CustomerBasicInfo.customerid = @CustomerId   )                                  ");
            string strSQL = sb.ToString();
            MyDB.AddParameters(day, "Day", SqlDbType.Int);
            MyDB.AddParameters(customerId, "CustomerId", SqlDbType.UniqueIdentifier);
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteReader();
            MyReader = MyDB.DBReader;
            while (MyReader.Read())
            {
                RemindCustomerFamilyMember remindCustomerFamilyMember = new RemindCustomerFamilyMember();
                remindCustomerFamilyMember.CompanyId = MyReader.GetGuid(0);
                remindCustomerFamilyMember.CustomerTitle = MyReader.GetString(1);
                remindCustomerFamilyMember.CustomerName = MyReader.GetString(2);
                remindCustomerFamilyMember.MemberName = MyReader.GetString(3);
                remindCustomerFamilyMember.Relationship = MyReader.GetString(4);
                remindCustomerFamilyMember.MBirthDate = MyReader.GetDateTime(5);
                remindCustomerFamilyMember.ResidualDay = MyReader.GetInt32(6);
                remindCustomerFamilyMember.Interval = MyReader.GetInt32(7);
                List.Add(remindCustomerFamilyMember);
            }
            MyDB.DBClose();
            return List;
        }

        public static DataSet FamilyMemberBirthDate1(Guid catId, Guid regionId, int day)
        {
            StringBuilder sb = new StringBuilder();
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            sb.Append("select TB_CustomerBasicInfo.CompanyId,TB_CustomerBasicInfo.CustomerTitle,TB_CustomerBasicInfo.CustomerName,TB_CustomerFamilyInfo.MemberName,TB_CustomerFamilyInfo.Relationship,TB_CustomerFamilyInfo.MBirthDate,datepart(dy,TB_CustomerFamilyInfo.MBirthDate) - datename(dy,getdate()) as ResidualDay");
            sb.Append("  from  dbo.TB_CustomerFamilyInfo left outer join dbo.TB_CustomerBasicInfo  on  dbo.TB_CustomerFamilyInfo.customerId =  dbo.TB_CustomerBasicInfo.customerid                   ");
            sb.Append("where                                                                                     ");
            sb.Append("datepart(dy,TB_CustomerFamilyInfo.MBirthDate) - datename(dy,getdate()) <=@Day          ");
            sb.Append("and                                                                                    ");
            sb.Append("datepart(dy,TB_CustomerFamilyInfo.MBirthDate) - datename(dy,getdate()) >=0        ");
            sb.Append("and                                                                                ");
            sb.Append("TB_CustomerFamilyInfo.MBirthDate <> '1800-1-1'                                       ");
            if ((catId.ToString() == "00000000-0000-0000-0000-000000000000") && (regionId.ToString() == "00000000-0000-0000-0000-000000000000"))
            {
                sb.Append("                   ");
            }
            else if (catId.ToString() == "00000000-0000-0000-0000-000000000000")
            {
                MyDB.AddParameters(regionId, "RegionId", SqlDbType.UniqueIdentifier);
                sb.Append("and  TB_CustomerFamilyInfo.customerId in                             ");
                sb.Append("(select TB_CustomerBasicInfo.customerid from TB_CustomerBasicInfo    ");
                sb.Append("where                                                                ");
                sb.Append("TB_CustomerBasicInfo.RegionId = @RegionId)                           ");
            }
            else if (regionId.ToString() == "00000000-0000-0000-0000-000000000000")
            {
                MyDB.AddParameters(catId, "CATId", SqlDbType.UniqueIdentifier);
                sb.Append("and  TB_CustomerFamilyInfo.customerId in                             ");
                sb.Append("(select TB_CustomerBasicInfo.customerid from TB_CustomerBasicInfo    ");
                sb.Append("where                                                                ");
                sb.Append("TB_CustomerBasicInfo.CATId = @CATId                                  ");
            }
            else
            {
                MyDB.AddParameters(catId, "CATId", SqlDbType.UniqueIdentifier);
                MyDB.AddParameters(regionId, "RegionId", SqlDbType.UniqueIdentifier);
                sb.Append("and  TB_CustomerFamilyInfo.customerId in                             ");
                sb.Append("(select TB_CustomerBasicInfo.customerid from TB_CustomerBasicInfo    ");
                sb.Append("where                                                                ");
                sb.Append("TB_CustomerBasicInfo.CATId = @CATId                                  ");
                sb.Append("and                                                                    ");
                sb.Append("TB_CustomerBasicInfo.RegionId = @RegionId)                           ");
            }
            string strSQL = sb.ToString();
            MyDB.AddParameters(day, "Day", SqlDbType.Int);
            MyDB.DBSetSelect(strSQL);
            return MyDB.DBReturnDataSet();
        }

        public static ArrayList FamilyMemberBirthDateInNextYear(Guid customerId, int day)
        {
            StringBuilder sb = new StringBuilder();
            SqlDataReader MyReader = null;
            ArrayList List = new ArrayList();
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            sb.Append("SELECT TB_CustomerBasicInfo.CompanyId,                             ");
            sb.Append("\tTB_CustomerBasicInfo.CustomerTitle,                                 ");
            sb.Append("\tTB_CustomerBasicInfo.CustomerName,                                   ");
            sb.Append("\tTB_CustomerFamilyInfo.MemberName,                                    ");
            sb.Append("\tTB_CustomerFamilyInfo.Relationship,                                    ");
            sb.Append("\tTB_CustomerFamilyInfo.MBirthDate,                                        ");
            sb.Append("\tdatepart(dy,TB_CustomerFamilyInfo.MBirthDate)+ datepart(dy,dateadd(ms,-3,DATEADD(yy,  DATEDIFF(yy,0,getdate())+1,  0)))- datepart(dy,getdate()) as ResidualDay,datepart(yyyy,getdate())-datepart(yyyy,TB_CustomerFamilyInfo.MBirthDate) as Interval  ");
            sb.Append("from  dbo.TB_CustomerFamilyInfo left outer join dbo.TB_CustomerBasicInfo  on  dbo.TB_CustomerFamilyInfo.customerId =  dbo.TB_CustomerBasicInfo.customerid  ");
            sb.Append("WHERE MBirthDate in                                        ");
            sb.Append("\t(SELECT MBirthDate                                          ");
            sb.Append("\tFROM TB_CustomerFamilyInfo                                     ");
            sb.Append("\tWHERE                                                         ");
            sb.Append("\tdatepart(dy,MBirthDate) < datepart(dy,getdate())                  ");
            sb.Append("\t)                                                                ");
            sb.Append("\tand                                                                    ");
            sb.Append("\tdatepart(dy,dateadd(day,@Day,getdate())) - (datepart(dy,TB_CustomerFamilyInfo.MBirthDate))<=@Day     ");
            sb.Append("\tand                                                                                                 ");
            sb.Append("\tdatepart(dy,dateadd(day,@Day,getdate())) - (datepart(dy,TB_CustomerFamilyInfo.MBirthDate)) >=0          ");
            sb.Append("\tand                                                                                                        ");
            sb.Append("\tTB_CustomerFamilyInfo.MBirthDate <> '1800-1-1'                                                             ");
            sb.Append("\tand  TB_CustomerFamilyInfo.customerId in                                                                    ");
            sb.Append("\t\t(select TB_CustomerBasicInfo.customerid from TB_CustomerBasicInfo                                        ");
            sb.Append("\t\twhere                                                                                                   ");
            sb.Append("\t\tTB_CustomerBasicInfo.customerid = @CustomerId  )                                 ");
            string strSQL = sb.ToString();
            MyDB.AddParameters(day, "Day", SqlDbType.Int);
            MyDB.AddParameters(customerId, "CustomerId", SqlDbType.UniqueIdentifier);
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteReader();
            MyReader = MyDB.DBReader;
            while (MyReader.Read())
            {
                RemindCustomerFamilyMember remindCustomerFamilyMember = new RemindCustomerFamilyMember();
                remindCustomerFamilyMember.CompanyId = MyReader.GetGuid(0);
                remindCustomerFamilyMember.CustomerTitle = MyReader.GetString(1);
                remindCustomerFamilyMember.CustomerName = MyReader.GetString(2);
                remindCustomerFamilyMember.MemberName = MyReader.GetString(3);
                remindCustomerFamilyMember.Relationship = MyReader.GetString(4);
                remindCustomerFamilyMember.MBirthDate = MyReader.GetDateTime(5);
                remindCustomerFamilyMember.ResidualDay = MyReader.GetInt32(6);
                remindCustomerFamilyMember.Interval = MyReader.GetInt32(7);
                List.Add(remindCustomerFamilyMember);
            }
            MyDB.DBClose();
            return List;
        }

        public static Guid[] GetCustomerIdAry(Guid catId, Guid regionId)
        {
            SqlDataReader MyReader = null;
            ArrayList List = new ArrayList();
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            string strSQL = "";
            if ((catId.ToString() == "00000000-0000-0000-0000-000000000000") && (regionId.ToString() == "00000000-0000-0000-0000-000000000000"))
            {
                strSQL = "select CustomerId from TB_CustomerBasicInfo";
            }
            else if (catId.ToString() == "00000000-0000-0000-0000-000000000000")
            {
                MyDB.AddParameters(regionId, "RegionId", SqlDbType.UniqueIdentifier);
                strSQL = "select CustomerId from TB_CustomerBasicInfo where RegionId=@RegionId";
            }
            else if (regionId.ToString() == "00000000-0000-0000-0000-000000000000")
            {
                MyDB.AddParameters(catId, "CATId", SqlDbType.UniqueIdentifier);
                strSQL = "select CustomerId from TB_CustomerBasicInfo where CATId=@CATId";
            }
            else
            {
                MyDB.AddParameters(catId, "CATId", SqlDbType.UniqueIdentifier);
                MyDB.AddParameters(regionId, "RegionId", SqlDbType.UniqueIdentifier);
                strSQL = "select CustomerId from TB_CustomerBasicInfo where RegionId=@RegionId and CATId=@CATId";
            }
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteReader();
            MyReader = MyDB.DBReader;
            while (MyReader.Read())
            {
                Guid id = (Guid) MyReader[0];
                List.Add(id);
            }
            MyDB.DBClose();
            return (Guid[]) List.ToArray(typeof(Guid));
        }

        public static ArrayList GetCustomerIdAry(Guid catId, Guid regionId, Guid areaId)
        {
            SqlDataReader MyReader = null;
            ArrayList List = new ArrayList();
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            string strSQL = "";
            MyDB.AddParameters(catId, "CATId", SqlDbType.UniqueIdentifier);
            MyDB.AddParameters(regionId, "RegionId", SqlDbType.UniqueIdentifier);
            MyDB.AddParameters(areaId, "AreaId", SqlDbType.UniqueIdentifier);
            strSQL = "select CustomerId from TB_CustomerBasicInfo where RegionId=@RegionId and CATId=@CATId and AreaId=@AreaId";
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteReader();
            MyReader = MyDB.DBReader;
            while (MyReader.Read())
            {
                Guid id = (Guid) MyReader[0];
                List.Add(id);
            }
            MyDB.DBClose();
            return List;
        }

        public static int GetLevelDayCount(string strLevel)
        {
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(strLevel, "ConfigKey", SqlDbType.VarChar);
            string strSQL = "select ConfigValue from TB_sysConfig where ConfigKey = @ConfigKey";
            MyDB.DBSetSelect(strSQL);
            if (MyDB.DBExecuteScalar() == null)
            {
                MyDB.DBClose();
                return 30;
            }
            int i = Convert.ToInt32(MyDB.DBExecuteScalar());
            MyDB.DBClose();
            return i;
        }
    }
}

